package mainFiles;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;



/**
 * Contains the database operations necessary to operate the gym management system.
 * @author Emily Linniman
 * @version November 26, 2014
 *
 */
public class GmsDB {
	
	/** The user name to connect to the database server. */
	private static String userName = "_445team3";
	
	/** The password to connect to the database server. */
	private static String password = "mofnudti";
	
	/** The server name on which the database is located. */
	private static String serverName = "cssgate.insttech.washington.edu";
	
	/** The connection object to connect to the database. */
	private static Connection conn;
	
	/** A list of classes objects that represent classes within the database. */
	private List<Class> classList;
	
	/** A list of instructor objects that represent instructors within the database. */
	private List<Instructor> instructorList;
	
	/** A list of location objects that represent locations within the database. */
	private List<Location> locationList;
	
	/** A list of user objects that represent users within the database. */
	private List<User> userList;
	
	/**
	 * Creates a sql connection to MySQL using the properties for
	 * userid, password and server information.
	 * @throws SQLException
	 */
	public static void createConnection() throws SQLException {
		Properties connectionProps = new Properties();
		connectionProps.put("user", userName);
		connectionProps.put("password", password);

		conn = DriverManager.getConnection("jdbc:" + "mysql" + "://"
				+ serverName + "/", connectionProps);

		System.out.println("Connected to database");
	}

	/**
	 * Gets a list of class objects that represent the classes in the database. 
	 * @return a list of class objects that represent the classes in the database
	 * @throws SQLException
	 */
	public List<Class> getClasses() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select classID, className, classSizeAvailability, classPrice, classDay, classTime, classDescription, Location_locationID, Instructor_User_userID "
				+ "from _445team3.Class ";

		classList = new ArrayList<Class>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String id = rs.getString("classID");
				String name = rs.getString("className");
				int size = rs.getInt("classSizeAvailability");
				BigDecimal cost = rs.getBigDecimal("classPrice");
				String day = rs.getString("classDay");
				String time = rs.getString("classTime");
				String description = rs.getString("classDescription");
				String instructor = rs.getString("Instructor_User_userID");
				String location = rs.getString("Location_locationID");

				
				Class newClass = new Class(id, name, size, cost, day, time, description, instructor, location);
				classList.add(newClass);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return classList;
	}
	
	/**
	 * Gets a list of class objects which represent the classes in the database with the corresponding class ID
	 * @param classID the id to match the classes in the database to
	 * @return a list of class objects which have a matching id to the classes in the database with the same class id given
	 */
	public List<Class> getClasses(String classID) {
		List<Class> filterList = new ArrayList<Class>();
		
		try {
			classList = getClasses();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (Class currClass : classList) {
			if (currClass.getID().toLowerCase().contains(classID.toLowerCase())) {
				filterList.add(currClass);
			}
		}
		return filterList;
	}
	
	
	/**
	 * Adds the given class to the database
	 * @param newClass the class to add to the database
	 */
	public void addClass(Class newClass) {
		String sql = "insert into _445team3.Class values " + "(?, ?, ?, ?, ?, ?, ?, ?, ?); ";

		PreparedStatement preparedStatement = null;
		try {

			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, newClass.getID());
			preparedStatement.setString(2, newClass.getName());
			preparedStatement.setInt(3, newClass.getSize());
			preparedStatement.setBigDecimal(4, newClass.getCost());
			preparedStatement.setString(5, newClass.getDay());
			preparedStatement.setString(6, newClass.getTime());
			preparedStatement.setString(7, newClass.getDescription());
			preparedStatement.setString(8, newClass.getLocation());
			preparedStatement.setString(9, newClass.getInstructor());
			
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 
	}

	/**
	 * Updates the given class id and column name to the new data provided.
	 * @param id the id of the class to update
	 * @param columnName the column name within the class to update
	 * @param newData the data to set the column name within the class
	 */
	public void updateClass(String id, String columnName, Object newData) {
		
		String sql = "update _445team3.Class set " + columnName + " = ? where classID = ? ";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			if (newData instanceof String)
				preparedStatement.setString(1, (String) newData);
			else if (newData instanceof Integer)
				preparedStatement.setInt(1, (Integer) newData);
			else if (newData instanceof BigDecimal)
				preparedStatement.setBigDecimal(1, (BigDecimal) newData);
			preparedStatement.setString(2, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		
	}
	
	/**
	 * Deletes the class with the given id from the database
	 * @param id the idea of the class to delete
	 */
	public void deleteClass(String id) {
		String sql = "delete from _445team3.Class where classID = ?";
		PreparedStatement preparedStatement = null;
		
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, id);
			
			preparedStatement.execute();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		}
	}

	/**
	 * Gets a list of instructor objects which represent the instructors in the database
	 * @return a list of instructor objects
	 * @throws SQLException
	 */
	public List<Instructor> getInstructors() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select User_userID, instructorHoursInstructed, managerID "
				+ "from _445team3.Instructor ";

		instructorList = new ArrayList<Instructor>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String id = rs.getString("User_userID");
				int hours = rs.getInt("instructorHoursInstructed");
				String manager = rs.getString("managerID");

				Instructor newIns = new Instructor(id, hours, manager);
				instructorList.add(newIns);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return instructorList;
	}
	
	/**
	 * Gets a list of location objects which represent locations within the database.
	 * @return a list of location objects
	 * @throws SQLException
	 */
	public List<Location> getLocations() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select locationID, locationName, locationSize, locationOccupationLimit "
				+ "from _445team3.Location ";

		locationList = new ArrayList<Location>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String id = rs.getString("locationID");
				String name = rs.getString("locationName");
				int size = rs.getInt("locationSize");
				int limit = rs.getInt("locationOccupationLimit");

				Location newLoc = new Location(id, name, size, limit);
				locationList.add(newLoc);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return locationList;
	}
	
	/**
	 * Gets a list of user objects which represents users within the database
	 * @return a list of user objects
	 * @throws SQLException
	 */
	public List<User> getUsers() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select userID, userLastName, userFirstName, userEmail, userPassword, userGymRole "
				+ "from _445team3.User ";

		userList = new ArrayList<User>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String id = rs.getString("userID");
				String lastName = rs.getString("userLastName");
				String firstName = rs.getString("userFirstName");
				String email = rs.getString("userEmail");
				String password = rs.getString("userPassword");
				String userRole = rs.getString("userGymRole");
				
				User newUser = new User(id, firstName, lastName, email, password, userRole);
				userList.add(newUser);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return userList;
	}
	
	/**
	 * Gets a list of user objects which represent users in the database with the given user id
	 * @param id the id to match users from the database to
	 * @return a list of user objects with ids that match the given id
	 */
	public List<User> getUsers(String id) {
		List<User> filterList = new ArrayList<User>();
		
		try {
			userList = getUsers();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (User currUser : userList) {
			if (currUser.getID().equals(id)) {
				filterList.add(currUser);
			}
		}
		return filterList;
	}

}
